{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b57f54ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Invoice</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>Price</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Country</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>489434</td>\n",
       "      <td>85048</td>\n",
       "      <td>15CM CHRISTMAS GLASS BALL 20 LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>6.95</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009/12/1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323P</td>\n",
       "      <td>PINK CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009/12/1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323W</td>\n",
       "      <td>WHITE CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009/12/1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>489434</td>\n",
       "      <td>22041</td>\n",
       "      <td>RECORD FRAME 7\" SINGLE SIZE</td>\n",
       "      <td>48</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>2.10</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009/12/1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>489434</td>\n",
       "      <td>21232</td>\n",
       "      <td>STRAWBERRY CERAMIC TRINKET BOX</td>\n",
       "      <td>24</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>1.25</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009/12/1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Invoice StockCode                          Description  Quantity  \\\n",
       "0  489434     85048  15CM CHRISTMAS GLASS BALL 20 LIGHTS        12   \n",
       "1  489434    79323P                   PINK CHERRY LIGHTS        12   \n",
       "2  489434    79323W                  WHITE CHERRY LIGHTS        12   \n",
       "3  489434     22041         RECORD FRAME 7\" SINGLE SIZE         48   \n",
       "4  489434     21232       STRAWBERRY CERAMIC TRINKET BOX        24   \n",
       "\n",
       "      InvoiceDate  Price  Customer ID         Country       Date  \n",
       "0  2009/12/1 7:45   6.95        13085  United Kingdom  2009/12/1  \n",
       "1  2009/12/1 7:45   6.75        13085  United Kingdom  2009/12/1  \n",
       "2  2009/12/1 7:45   6.75        13085  United Kingdom  2009/12/1  \n",
       "3  2009/12/1 7:45   2.10        13085  United Kingdom  2009/12/1  \n",
       "4  2009/12/1 7:45   1.25        13085  United Kingdom  2009/12/1  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import warnings\n",
    "import os \n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data_path = \"/Users/collinsliu/learning/jobs/self advance/course/platform_order.csv\"\n",
    "data_df = pd.read_csv(data_path,\n",
    "            encoding='utf-8')\n",
    "data_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "16a6c531",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44876"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoice_list = []\n",
    "for invoice, item in data_df.groupby(data_df[\"Invoice\"]):\n",
    "    invoice_list.append(invoice)\n",
    "len(invoice_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cbde673e",
   "metadata": {},
   "outputs": [],
   "source": [
    "cancel_invoice = np.random.rand(len(invoice_list))\n",
    "cancel_invoice_str = [\" \" for _ in range(len(invoice_list))]\n",
    "for index,item in enumerate(cancel_invoice):\n",
    "    if item < 0.5:\n",
    "        cancel_invoice_str[index] = \"False\"\n",
    "    else:\n",
    "        cancel_invoice_str[index] = \"True\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "98585f15",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_cancel = pd.DataFrame({\"Invoice\":invoice_list,\n",
    "              \"Cancel_invoice\": cancel_invoice_str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "508f0b6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1_df = data_df.merge(data_cancel,how=\"left\",on=\"Invoice\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4f46c0ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_purchase_list = np.floor(np.random.uniform(low=0,high=20,size=len(invoice_list)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3d18c0a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_first = pd.DataFrame({\"Invoice\":invoice_list,\n",
    "                           \"First_purchase_delta\":first_purchase_list})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d51d01ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "data2_df = data1_df.merge(data_first,how=\"left\",on=\"Invoice\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d7472e5e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Invoice</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>Price</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Country</th>\n",
       "      <th>Date</th>\n",
       "      <th>Cancel_invoice</th>\n",
       "      <th>First_purchase_delta</th>\n",
       "      <th>First_purchase</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>489434</td>\n",
       "      <td>85048</td>\n",
       "      <td>15CM CHRISTMAS GLASS BALL 20 LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>6.95</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>True</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2009-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323P</td>\n",
       "      <td>PINK CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>True</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2009-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323W</td>\n",
       "      <td>WHITE CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>True</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2009-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>489434</td>\n",
       "      <td>22041</td>\n",
       "      <td>RECORD FRAME 7\" SINGLE SIZE</td>\n",
       "      <td>48</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>2.10</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>True</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2009-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>489434</td>\n",
       "      <td>21232</td>\n",
       "      <td>STRAWBERRY CERAMIC TRINKET BOX</td>\n",
       "      <td>24</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>1.25</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>True</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2009-07-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Invoice StockCode                          Description  Quantity  \\\n",
       "0  489434     85048  15CM CHRISTMAS GLASS BALL 20 LIGHTS        12   \n",
       "1  489434    79323P                   PINK CHERRY LIGHTS        12   \n",
       "2  489434    79323W                  WHITE CHERRY LIGHTS        12   \n",
       "3  489434     22041         RECORD FRAME 7\" SINGLE SIZE         48   \n",
       "4  489434     21232       STRAWBERRY CERAMIC TRINKET BOX        24   \n",
       "\n",
       "      InvoiceDate  Price  Customer ID         Country       Date  \\\n",
       "0  2009/12/1 7:45   6.95        13085  United Kingdom 2009-12-01   \n",
       "1  2009/12/1 7:45   6.75        13085  United Kingdom 2009-12-01   \n",
       "2  2009/12/1 7:45   6.75        13085  United Kingdom 2009-12-01   \n",
       "3  2009/12/1 7:45   2.10        13085  United Kingdom 2009-12-01   \n",
       "4  2009/12/1 7:45   1.25        13085  United Kingdom 2009-12-01   \n",
       "\n",
       "  Cancel_invoice  First_purchase_delta First_purchase  \n",
       "0           True                   5.0     2009-07-01  \n",
       "1           True                   5.0     2009-07-01  \n",
       "2           True                   5.0     2009-07-01  \n",
       "3           True                   5.0     2009-07-01  \n",
       "4           True                   5.0     2009-07-01  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2_df[\"Date\"] = pd.to_datetime(data2_df[\"Date\"])\n",
    "data2_df[\"First_purchase\"] = \\\n",
    "data2_df.apply(lambda row: row[\"Date\"]\n",
    "               -pd.DateOffset(months=row[\"First_purchase_delta\"]),axis=1)\n",
    "data2_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "20652780",
   "metadata": {},
   "outputs": [],
   "source": [
    "data2_df = data2_df.drop(\"First_purchase_delta\",axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6e62a9df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Invoice</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>Price</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Country</th>\n",
       "      <th>Date</th>\n",
       "      <th>Cancel_invoice</th>\n",
       "      <th>First_purchase</th>\n",
       "      <th>GMV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>489434</td>\n",
       "      <td>85048</td>\n",
       "      <td>15CM CHRISTMAS GLASS BALL 20 LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>6.95</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>True</td>\n",
       "      <td>2009-07-01</td>\n",
       "      <td>83.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323P</td>\n",
       "      <td>PINK CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>True</td>\n",
       "      <td>2009-07-01</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323W</td>\n",
       "      <td>WHITE CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>True</td>\n",
       "      <td>2009-07-01</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>489434</td>\n",
       "      <td>22041</td>\n",
       "      <td>RECORD FRAME 7\" SINGLE SIZE</td>\n",
       "      <td>48</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>2.10</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>True</td>\n",
       "      <td>2009-07-01</td>\n",
       "      <td>100.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>489434</td>\n",
       "      <td>21232</td>\n",
       "      <td>STRAWBERRY CERAMIC TRINKET BOX</td>\n",
       "      <td>24</td>\n",
       "      <td>2009/12/1 7:45</td>\n",
       "      <td>1.25</td>\n",
       "      <td>13085</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>True</td>\n",
       "      <td>2009-07-01</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Invoice StockCode                          Description  Quantity  \\\n",
       "0  489434     85048  15CM CHRISTMAS GLASS BALL 20 LIGHTS        12   \n",
       "1  489434    79323P                   PINK CHERRY LIGHTS        12   \n",
       "2  489434    79323W                  WHITE CHERRY LIGHTS        12   \n",
       "3  489434     22041         RECORD FRAME 7\" SINGLE SIZE         48   \n",
       "4  489434     21232       STRAWBERRY CERAMIC TRINKET BOX        24   \n",
       "\n",
       "      InvoiceDate  Price  Customer ID         Country       Date  \\\n",
       "0  2009/12/1 7:45   6.95        13085  United Kingdom 2009-12-01   \n",
       "1  2009/12/1 7:45   6.75        13085  United Kingdom 2009-12-01   \n",
       "2  2009/12/1 7:45   6.75        13085  United Kingdom 2009-12-01   \n",
       "3  2009/12/1 7:45   2.10        13085  United Kingdom 2009-12-01   \n",
       "4  2009/12/1 7:45   1.25        13085  United Kingdom 2009-12-01   \n",
       "\n",
       "  Cancel_invoice First_purchase    GMV  \n",
       "0           True     2009-07-01   83.4  \n",
       "1           True     2009-07-01   81.0  \n",
       "2           True     2009-07-01   81.0  \n",
       "3           True     2009-07-01  100.8  \n",
       "4           True     2009-07-01   30.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2_df[\"GMV\"] = data2_df[\"Quantity\"]*data2_df[\"Price\"]\n",
    "data2_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9f4b5e4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "data2_df.to_csv('customer_churn.csv',\n",
    "                index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ef8bc8c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
